<?php    
    use SharedPHP\UnitConverter;
        
    class WorkoutControl extends Control {        
        protected static $_callable = array(
            "delete",
            "load",
            "oneRepMax",
            "save",
            "search"
        );
        
        /**
         * Funktio poistaa harjoituksen tiedot tietokannasta.
         *
         * @return bool
         */
        public static function delete($data) {
            $result = false;
            
            DB::begin();
            
            try {
                (new WorkoutModel(array(
                    "hashid" => arrGet($data,"hashid")
                )))->delete();
                
                $result = true;
            } catch (Exception $e) {
                DB::rollback();
                throw $e;
            }
            
            DB::commit();
            
            return $result;
        }
        
        /**
         * Funktio hakee harjoituksen tiedot tietokannasta.
         *
         * @return  array
         */
        public static function load($data) {
            $result = array();
            
            $workout = new WorkoutModel(array(
                "hashid" => arrGet($data,"hashid")
            ));
            
            # Onko location asetettu
            $locationhashid = null;
            $locationtext   = null;
            if ($workout->locationid) {
                $location = new LocationModel($workout->locationid);
                $locationhashid = $location->hashid;
                $locationtext   = $location->location;
            }
            
            # Onko harjoitusohjelma asetettu
            $trainingProgramHashid  = null;
            $trainingProgramName    = null;
            if ($workout->training_programid) {
                $tp = new TrainingProgramModel($workout->training_programid);
                $trainingProgramHashid = $tp->hashid;
                $trainingProgramName   = $tp->program_name;
            }
            
            # Onko harjoituspohja asetettu
            $templateHashid     = null;
            $templateExtratext  = null;
            if ($workout->templateid) {
                $template = new TemplateModel($workout->templateid);
                $templateHashid     = $template->hashid;
                $templateExtratext  = $template->extratext;
            }
            
            # Onko painotiedot syötetty
            $userWeightArr = array();
            if ($workout->user_weightid) {
                $weight = new UserWeightModel($workout->user_weightid);
                $userWeightArr["hashid"]            = $weight->hashid;
                $userWeightArr["weight"]            = (($weight->unit == "kg") ? $weight->weight_kg : $weight->weight_lb);
                $userWeightArr["weight_kg"]         = $weight->weight_kg;
                $userWeightArr["weight_lb"]         = $weight->weight_lb;
                $userWeightArr["unit"]              = $weight->unit;
                $userWeightArr["measurement_time"]  = $weight->measurement_time;
                $userWeightArr["fatpercent"]        = $weight->fatpercent;
                $userWeightArr["date"]              = $weight->date;
                $userWeightArr["updated"]           = $weight->updated;
                $userWeightArr["rowstamp"]          = $weight->rowstamp;
            }
            
            # Harjoituksen perustiedot
            $result["hashid"]                   = $workout->hashid;
            $result["locationhashid"]           = $locationhashid;
            $result["locationtext"]             = $locationtext;
            $result["training_program_hashid"]  = $trainingProgramHashid;
            $result["training_program_name"]    = $trainingProgramName;
            $result["templatehashid"]           = $templateHashid;
            $result["template_extratext"]       = $templateExtratext;
            $result["user_weight"]              = $userWeightArr;
            $result["extratext"]                = $workout->extratext;
            $result["starttime"]                = $workout->starttime;
            $result["endtime"]                  = $workout->endtime;
            $result["updated"]                  = $workout->updated;
            $result["rowstamp"]                 = $workout->rowstamp;
            
            # Harjoitteet
            $cache = array();
            $result["exercises"] = array();
            foreach ((array)$workout->workout_exercise as $exercise) {
                # Toistot
                $sets = array();
                foreach ((array)$exercise->workout_exercise_set as $set) {
                    # Löytyykö cachesta
                    if (!isset($cache[$set->exerciseid])) {
                        $cache[$set->exerciseid] = new ExerciseModel($set->exerciseid);
                    }
                    
                    $sets[] = array(
                        "exerciseid"        => $set->exerciseid,
                        "exercisetext"      => $cache[$set->exerciseid]->exercise,
                        "category"          => $cache[$set->exerciseid]->category,
                        "musclegroup"       => $cache[$set->exerciseid]->musclegroup,
                        "repetitions"       => $set->repetitions,
                        "weight"            => (($exercise->unit == "kg") ? $set->weight_kg : $set->weight_lb),
                        "weight_kg"         => $set->weight_kg,
                        "weight_lb"         => $set->weight_lb,
                        "repetitions_type"  => $set->repetitions_type,
                        "barbell_type"      => $set->barbell_type,
                        "rest_interval_sec" => $set->rest_interval_sec
                    );
                }
                
                $result["exercises"][] = array(                    
                    "unit"              => $exercise->unit,
                    "extratext"         => $exercise->extratext,
                    "special"           => $exercise->special,
                    "gear_belt"         => $exercise->gear_belt,
                    "gear_knee_wraps"   => $exercise->gear_knee_wraps,
                    "gear_shirt"        => $exercise->gear_shirt,
                    "gear_suit"         => $exercise->gear_suit,
                    "gear_wrist_straps" => $exercise->gear_wrist_straps,
                    "gear_wrist_wraps"  => $exercise->gear_wrist_wraps,
                    "sets"              => $sets
                );
            }
            
            # Merkinnät
            $result["records"] = array();
            foreach ((array)$workout->workout_record as $record) {
                $result["records"][] = array(
                    "record" => $record->record
                );
            }
            
            return $result;
        }
        
        /**
         * Tallentaa harjoituksen tiedot ja palauttaa harjoitteen hashid:n.
         *
         * @param   array   $data   Tiedot
         * @return  string
         */
        public static function save($data) {
            DB::begin();
            
            try {
                $arr = null;
                $hashid = arrGet($data,"hashid");
                if (isset($hashid)) {
                    $arr = array("hashid" => $hashid);
                }
                
                $workout = new WorkoutModel($arr);
                
                # Aseta userid jos luodaan uutta
                if (!isset($hashid)) {
                    $workout->userid = LoginControl::get("userid");
                } else {
                    # Poista muokkauksen yhteydessa vanhat alitietueet
                    $workout->deleteChilds();
                    $workout->unsetChilds();
                    
                    # Aseta päivitysaika
                    $workout->updated = date("Y-m-d H:i:s");                    
                }
                
                $locationid = null; 
                $locationtext = arrGet($data,"locationtext");
                $locationhashid = arrGet($data,"locationhashid");
                
                if (mb_strlen($locationtext) > 0) {
                    $location = new LocationModel();
                    $location->userid   = LoginControl::get("userid");
                    $location->location = $locationtext;
                    $locationid         = $location->write();
                } else if ($locationhashid) {                    
                    $location = new LocationModel(array(
                        "hashid" => $locationhashid
                    ));
                    $locationid = $location->id;
                }
                
                # Tarkasta käyttöoikeus harjoitusohjelmaan
                $trainingProgramId = null;
                $trainingProgramHashid = arrGet($data,"training_program_hashid");
                if ($trainingProgramHashid) {
                    $trainingProgramId = (new TrainingProgramModel(array(
                        "hashid" => $trainingProgramHashid
                    )))->id;
                }
                
                # Tarkasta käyttöoikeus harjoituspohjaan
                $templateId = null;
                $templateHashid = arrGet($data,"templatehashid");
                if ($templateHashid) {
                    $templateId = (new TemplateModel(array(
                        "hashid" => $templateHashid
                    )))->id;
                }
                
                $workout->locationid            = $locationid;
                $workout->training_programid    = $trainingProgramId;
                $workout->templateid            = $templateId;
                $workout->extratext             = $data;
                $workout->starttime             = $data;
                $workout->endtime               = $data;
                
                # Lisää painotiedot tarvittaessa
                $userWeightArr  = (array)arrGet($data,"user_weight");
                $fatPercent     = arrGet($userWeightArr,"fatpercent","");
                $weightKg       = arrGet($userWeightArr,"weight_kg","");
                $weightLb       = arrGet($userWeightArr,"weight_lb","");
                $weight         = arrGet($userWeightArr,"weight","");
                
                $max = max(array(
                    mb_strlen($fatPercent),
                    mb_strlen($weightKg),
                    mb_strlen($weightLb),
                    mb_strlen($weight)
                ));
                if ($max > 0) {
                    $userWeightId   = $workout->user_weightid;
                    $userWeightDate = date("Y-m-d",strtotime($workout->starttime));
                    
                    # Hae aikaisempi mittaustieto jos löytyy
                    $query = DB::preparedQuery("
                        SELECT id
                        FROM user_weight
                        WHERE userid=:userid
                          AND measurement_time=:measurementTime 
                          AND date=:date
                        ORDER BY id DESC
                        LIMIT 1
                    ",array(
                        "userid"            => LoginControl::get("userid"),
                        "measurementTime"   => (string)arrGet($userWeightArr,"measurement_time",""),
                        "date"              => $userWeightDate
                    ));
                    if (DB::numRows($query) == 1) {
                        $userWeightId = DB::result($query,0,"id");
                        DB::freeResult($query);
                    }
                    
                    $userWeight = new UserWeightModel($userWeightId);
                    $userWeight->userid             = LoginControl::get("userid");
                    $userWeight->measurement_time   = $userWeightArr;
                    $userWeight->fatpercent         = $userWeightArr;
                    $userWeight->date               = $userWeightDate;
                    
                    # Aseta painoyksikkö
                    $weightUnit = (string)arrGet($userWeightArr,"unit","");
                    if (!$weightUnit) {
                        $weightUnit = LoginControl::get("default_unit");
                    }
                    $userWeight->unit = $weightUnit;
                    
                    if (mb_strlen($weight) > 0) {
                        # Muunna yksiköt
                        switch ($userWeight->unit) {
                            case "kg":
                                $weightKg = $weight;
                                $weightLb = round(UnitConverter::kgToLb($weight),2);
                                break;
                            
                            case "lb":
                                $weightKg = round(UnitConverter::lbToKg($weight),2);
                                $weightLb = $weight;
                                break;
                        }
                    }
                    
                    # Tallennetaan käyttäjän käyttämä painoyksikkö ensin
                    if (mb_strlen($weightKg) > 0 || mb_strlen($weightLb) > 0) {
                        switch ($userWeight->unit) {
                            case "kg":
                                $userWeight->weight_kg = $weightKg;
                                $userWeight->weight_lb = $weightLb;
                                break;
                            
                            case "lb":
                                $userWeight->weight_lb = $weightLb;
                                $userWeight->weight_kg = $weightKg;
                                break;
                        }
                    } else {
                        $userWeight->weight_kg = null;
                        $userWeight->weight_lb = null;
                    }
                    
                    # Aseta päivitysaika tarvittaessa
                    if ($userWeightId) {
                        $userWeight->updated = date("Y-m-d H:i:s");
                    }
                    
                    $workout->user_weightid = $userWeight->write();
                } else {
                    $workout->user_weightid = null;
                }
                
                # Harjoitteet
                $c = 0;
                $cache = array();
                foreach ((array)arrGet($data,"exercises") as $exercise) {                    
                    RequestHandler::addIteration("WorkoutExerciseModel");
                    
                    $exrcs = new WorkoutExerciseModel();
                    $exrcs->unit                = $exercise;
                    $exrcs->extratext           = $exercise;
                    $exrcs->gear_belt           = $exercise;
                    $exrcs->gear_knee_wraps     = $exercise;
                    $exrcs->gear_shirt          = $exercise;
                    $exrcs->gear_suit           = $exercise;
                    $exrcs->gear_wrist_straps   = $exercise;
                    $exrcs->gear_wrist_wraps    = $exercise;
                    
                    $special = arrGet($exercise,"special","");
                    if (mb_strlen($special) > 0) {
                        $exrcs->special = $special;
                    }
                    
                    # Toistot
                    $c2 = 0;
                    foreach ((array)arrGet($exercise,"sets") as $set) {
                        RequestHandler::addIteration("WorkoutExerciseSetModel");
                        
                        # Löytyykö cachesta
                        $exerciseid = arrGet($set,"exerciseid");
                        if ($exerciseid && !isset($cache[$exerciseid])) {
                            $cache[$exerciseid] = (new ExerciseModel($exerciseid))->id;
                        }                        
                        
                        $exerciseset = new WorkoutExerciseSetModel();
                        $exerciseset->exerciseid        = $exerciseid;
                        $exerciseset->repetitions       = $set;
                        $exerciseset->repetitions_type  = $set;
                        
                        $weightKg   = arrGet($set,"weight_kg","");
                        $weightLb   = arrGet($set,"weight_lb","");
                        $weight     = arrGet($set,"weight","");
                        if (mb_strlen($weight) > 0) {
                            # Muunna yksiköt
                            switch ($exrcs->unit) {
                                case "kg":
                                    $weightKg = $weight;
                                    $weightLb = round(UnitConverter::kgToLb($weight),2);
                                    break;
                                
                                case "lb":
                                    $weightKg = round(UnitConverter::lbToKg($weight),2);
                                    $weightLb = $weight;
                                    break;
                            }
                        }
                        
                        # Tallennetaan käyttäjän käyttämä painoyksikkö ensin
                        switch ($exrcs->unit) {
                            case "kg":
                                $exerciseset->weight_kg = $weightKg;
                                $exerciseset->weight_lb = $weightLb;
                                break;
                            
                            case "lb":
                                $exerciseset->weight_lb = $weightLb;
                                $exerciseset->weight_kg = $weightKg;
                                break;
                        }
                        
                        # Levytangon tyyppi
                        $barbellType = arrGet($set,"barbell_type","");
                        if (mb_strlen($barbellType) > 0) {
                            $exerciseset->barbell_type = $barbellType;
                        }
                        
                        # Lepotauko
                        $rest = arrGet($set,"rest_interval_sec","");
                        if (mb_strlen($rest) > 0) {
                            $exerciseset->rest_interval_sec = $rest;
                        }
                        
                        $exrcs->workout_exercise_set = $exerciseset;
                        
                        if (++$c2 >= 100) {
                            break;
                        }
                    }
                    RequestHandler::clearIteration("WorkoutExerciseSetModel");
                    
                    $workout->workout_exercise = $exrcs;
                    
                    if (++$c >= 100) {
                        break;
                    }
                }
                RequestHandler::clearIteration("WorkoutExerciseModel");
                
                # Merkinnät
                $c = 0;
                foreach ((array)arrGet($data,"records") as $record) {
                    RequestHandler::addIteration("WorkoutRecordModel");
                    if (mb_strlen(arrGet($record,"record","")) > 0) {
                        $rcd = new WorkoutRecordModel();
                        $rcd->record = $record;
                        
                        $workout->workout_record = $rcd;
                        
                        if (++$c >= 100) {
                            break;
                        }
                    }
                }
                RequestHandler::clearIteration("WorkoutRecordModel");
                                
                $workout->write();
                $hashid = $workout->hashid;
            } catch (Exception $e) {
                DB::rollback();
                throw $e;
            }
            
            DB::commit();
            
            return $hashid;
        }
        
        /**
         * Etsii harjoitteita tietokannasta (maksimissaan 10 000 kpl).
         *
         * @param   array   $data   Hakuparametrit
         * @return  array
         */
        public static function search($data) {
            $result = array(                
                "list"          => array(),
                "totalCount"    => 0
            );            
            
            $params = array(
                "userid" => LoginControl::get("userid")
            );
            $where = "";
            
            # Alkupvm
            if (isset($data["startdate"]) && $data["startdate"]) {
                $d = DateTime::createFromFormat("Y-m-d",$data["startdate"]);
                if ($d) {
                    $params["startdate"] = $d->format("Y-m-d");
                    $where .= " AND DATE(workout.starttime)>=:startdate";
                }
            }
            
            # Loppupvm
            if (isset($data["enddate"]) && $data["enddate"]) {
                $d = DateTime::createFromFormat("Y-m-d",$data["enddate"]);
                if ($d) {
                    $params["enddate"] = $d->format("Y-m-d");
                    $where .= " AND DATE(workout.endtime)<=:enddate";
                }
            }
            
            # Harjoitteen id
            if (isset($data["exerciseid"]) && $data["exerciseid"]) {
                $params["exerciseid"] = $data["exerciseid"];
                $where .= " AND exercise.id IN (:exerciseid)";
            }
            
            # Harjoitteen lihasryhmä
            if (isset($data["musclegroup"]) && $data["musclegroup"]) {
                $params["musclegroup"] = $data["musclegroup"];
                $where .= " AND exercise.musclegroup IN (:musclegroup)";
            }
            
            # Harjoitusohjelman hashid
            if (isset($data["training_program_hashid"]) && $data["training_program_hashid"]) {
                $params["training_program_hashid"] = $data["training_program_hashid"];
                $where .= " AND training_program.hashid IN (:training_program_hashid)";
            }
            
            # Harjoituspohjan hashid
            if (isset($data["templatehashid"]) && $data["templatehashid"]) {
                $params["templatehashid"] = $data["templatehashid"];
                $where .= " AND template.hashid IN (:templatehashid)";
            }
            
            # Painotiedon hashid
            if (isset($data["user_weight_hashid"]) && $data["user_weight_hashid"]) {
                $params["user_weight_hashid"] = $data["user_weight_hashid"];
                $where .= " AND user_weight.hashid IN (:user_weight_hashid)";
            }
            
            # Positio ja haettavien rivien määrä
            $pos = 0;
            $count = null;
            if (isset($data["pos"]) && (int)$data["pos"] >= 0) {
                $pos = (int)$data["pos"]; 
            }
            if (isset($data["count"])) {
                $count = (int)$data["count"];
                if ($count <= 0) {
                    return $result;
                }
            }
            
            # Suorita haku
            $query = DB::preparedQuery("
                SELECT workout.hashid,
                  workout.extratext,
                  workout.starttime,
                  TIMESTAMPDIFF(MINUTE,workout.starttime,workout.endtime) AS duration_min,
                  COUNT(DISTINCT workout_exercise.id) AS exercises,
                  COUNT(workout_exercise_set.id) AS sets,
                  SUM(workout_exercise_set.repetitions) AS repetitions
                FROM workout
                LEFT JOIN training_program ON training_program.id = workout.training_programid
                LEFT JOIN template ON template.id = workout.templateid
                LEFT JOIN user_weight ON user_weight.id = workout.user_weightid
                LEFT JOIN workout_exercise ON workout_exercise.workoutid = workout.id
                LEFT JOIN workout_exercise_set ON workout_exercise_set.workout_exerciseid = workout_exercise.id
                LEFT JOIN exercise ON exercise.id = workout_exercise_set.exerciseid
                WHERE workout.userid=:userid{$where}
                GROUP BY workout.id
                ORDER BY workout.starttime DESC
                LIMIT 10000
                ",$params
            );
            $numrows = DB::numRows($query);
            if ($numrows > 0) {
                if (!isset($count)) {
                    $count = $numrows-$pos;
                }
                
                if ($count > 0) {
                    if ($pos > 0) {
                        DB::dataSeek($query,$pos);
                    }
                    
                    $c = 0;
                    while ($row = DB::fetchAssoc($query)) {
                        $result["list"][] = $row;
                        if (++$c >= $count) {
                            break;
                        }
                    }
                }
                DB::freeResult($query);
                
                $result["totalCount"] = $numrows;
            }
            
            return $result;
        }
        
        /**
         * Etsii harjoitteiden yhden toiston maksimin kehitystä.
         *
         * @param   array   $data   Hakuparametrit
         * @return  array
         */
        public static function oneRepMax($data) {
            $result = array();
            
            $params = array(
                "userid" => LoginControl::get("userid")
            );
            $where = "";
            
            # Alkupvm
            if (isset($data["startdate"]) && $data["startdate"]) {
                $d = DateTime::createFromFormat("Y-m-d",$data["startdate"]);
                if ($d) {
                    $params["startdate"] = $d->format("Y-m-d");
                    $where .= " AND DATE(workout.starttime)>=:startdate";
                }
            }
            
            # Loppupvm
            if (isset($data["enddate"]) && $data["enddate"]) {
                $d = DateTime::createFromFormat("Y-m-d",$data["enddate"]);
                if ($d) {
                    $params["enddate"] = $d->format("Y-m-d");
                    $where .= " AND DATE(workout.endtime)<=:enddate";
                }
            }
            
            # Harjoitteen id
            if (isset($data["exerciseid"]) && $data["exerciseid"]) {
                $params["exerciseid"] = $data["exerciseid"];
                $where .= " AND exercise.id IN (:exerciseid)";
            }
            
            # Toiston tyyppi
            if (isset($data["reptype"]) && $data["reptype"]) {
                $params["reptype"] = $data["reptype"];
                $where .= " AND workout_exercise_set.repetitions_type IN (:reptype)";
            }
            
            # Muodosta hakulauseke
            $sql = "
                SELECT workout.hashid,
                  workout.extratext,
                  DATE(workout.starttime) AS startdate, 
                  exercise.id AS exerciseid,
                  exercise.exercise AS exercisetext,
                  workout_exercise_set.barbell_type,
                  workout_exercise_set.repetitions,
                  workout_exercise_set.repetitions_type,
                  workout_exercise_set.weight_kg,
                  workout_exercise_set.weight_lb,
                  workout_exercise_set.weight_kg AS calculated_kg,
                  workout_exercise_set.weight_lb AS calculated_lb,
                  0 AS calculated
                FROM workout_exercise_set
                INNER JOIN exercise ON exercise.id = workout_exercise_set.exerciseid
                INNER JOIN workout_exercise ON workout_exercise.id = workout_exercise_set.workout_exerciseid
                INNER JOIN workout ON workout.id = workout_exercise.workoutid
                WHERE workout.userid=:userid
                  AND workout_exercise_set.repetitions = 1{$where}
            ";
            
            # Otetaan mukaan myös lasketut yhden toiston maksimit
            if (isset($data["calculated"]) && $data["calculated"]) {
                $sql .= "
                    UNION ALL
                    
                    SELECT workout.hashid,
                      workout.extratext,
                      DATE(workout.starttime) AS startdate, 
                      exercise.id AS exerciseid,
                      exercise.exercise AS exercisetext,
                      workout_exercise_set.barbell_type,
                      workout_exercise_set.repetitions,
                      workout_exercise_set.repetitions_type,
                      workout_exercise_set.weight_kg,
                      workout_exercise_set.weight_lb,
                      ROUND(
                        workout_exercise_set.weight_kg *
                          workout_exercise_set.repetitions *
                          0.0333 +
                          workout_exercise_set.weight_kg,
                        2
                      ) AS calculated_kg,
                      ROUND(
                        workout_exercise_set.weight_lb *
                          workout_exercise_set.repetitions *
                          0.0333 +
                          workout_exercise_set.weight_lb,
                        2
                      ) AS calculated_lb,
                      1 AS calculated
                    FROM workout_exercise_set
                    INNER JOIN exercise ON exercise.id = workout_exercise_set.exerciseid
                    INNER JOIN workout_exercise ON workout_exercise.id = workout_exercise_set.workout_exerciseid
                    INNER JOIN workout ON workout.id = workout_exercise.workoutid
                    WHERE workout.userid=:userid
                      AND workout_exercise_set.repetitions > 1{$where}
                ";
            }
            
            $sql .= "
                ORDER BY calculated_kg DESC,
                  exerciseid,
                  startdate DESC
                LIMIT 500
            ";
            
            # Suorita haku
            $c = 0;
            $fetch = array();            
            $query = DB::preparedQuery($sql,$params);
            if (DB::numRows($query) > 0) {
                $x = 0;
                while ($row = DB::fetchAssoc($query)) {
                    $id     = $row["exerciseid"];
                    $date   = $row["startdate"];
                    $weight = $row["calculated_kg"];
                    $type   = $row["barbell_type"];
                    
                    if (!$type) {
                        $type = "none";
                    }
                    
                    if (isset($fetch[$id][$type][$date])) {
                        if ($weight <= $fetch[$id][$type][$date]["weight"]) {
                            continue;
                        } else {
                            unset($result[$fetch[$id][$type][$date]["pos"]]);
                        }
                    }
                    
                    $result[$x] = $row;
                    $fetch[$id][$type][$date] = array(
                        "pos"       => $x,
                        "weight"    => $weight
                    );
                    
                    $x++;
                    $c++;
                    if ($c == 20) {
                        break;
                    }    
                }
                $result = array_values($result);
            }
            DB::freeResult($query);
            
            return $result;
        }
    }